from pydantic import BaseModel
from typing import List, Optional
import sqlalchemy
from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

from utils import calculate_md5_hash


# 定义学生数据模型
class Student(BaseModel):
    uid: str
    stuNum: str
    stuName: str


# 定义班级数据模型
class ClassStuData(BaseModel):
    classTeacher: str
    classTeacherId: str
    classStuList: List[Student]



# 数据库连接配置
DATABASE_URL = "mysql+pymysql://root:123456@localhost:3306/smart_class"

# 创建数据库引擎
engine = sqlalchemy.create_engine(DATABASE_URL)


def get_class_data(class_id):
    # 查询班导师名字
    sql_teacher = f"""
        SELECT ud.username AS classTeacher, ud.uid AS classTeacherId
        FROM class c
        LEFT JOIN user_detail ud ON c.class_teacher_id = ud.uid
        WHERE c.class_id = '{class_id}'
        """

    # 查询学生列表
    sql_students = f"""
        SELECT s.uid, s.stu_num AS stuNum, ud.username AS stuName
        FROM stu_detail s
        LEFT JOIN user_detail ud ON s.uid = ud.uid
        WHERE s.stu_class_id = '{class_id}'
        """

    # 执行查询
    with engine.connect() as conn:
        # 查询班导师
        result_teacher = conn.execute(sqlalchemy.text(sql_teacher)).fetchone()
        # 查询学生列表
        result_students = conn.execute(sqlalchemy.text(sql_students)).fetchall()

    # 处理查询结果
    classTeacher = result_teacher.classTeacher if result_teacher else None
    classTeacherId = result_teacher.classTeacherId if result_teacher else None
    classStuList = [Student(uid=row.uid, stuNum=row.stuNum, stuName=row.stuName) for row in result_students]

    # 创建班级数据模型实例
    class_data = ClassStuData(classTeacher=classTeacher,
                              classTeacherId=classTeacherId,
                              classStuList=classStuList)

    return class_data


# 定义用户注册数据模型
class UserRegister(BaseModel):
    user_id: str
    password: str
    role: str  # 可以是 'student' 或 'teacher'


def register_user(user_data: UserRegister):
    # 开始一个数据库事务
    with engine.begin() as connection:
        try:
            # 插入用户信息到userinfo表
            insert_userinfo = text(
                """
                INSERT INTO userinfo (uid, password) VALUES (:user_id, :password)
                """
            )
            connection.execute(insert_userinfo, {'user_id': user_data.user_id,
                                                 'password': calculate_md5_hash(user_data.password)})

            # 插入用户角色到user_role表
            insert_user_role = text(
                """
                INSERT INTO user_role (uid, role) VALUES (:user_id, :role)
                """
            )
            connection.execute(insert_user_role, {'user_id': user_data.user_id, 'role': user_data.role})

            # 根据角色插入详细信息
            if user_data.role == 'student':
                # 插入学生详细信息到stu_detail表
                insert_stu_detail = text(
                    """
                    INSERT INTO stu_detail (uid, stu_num) VALUES (:user_id, :stu_num)
                    """
                )
                # 假设学生编号是必须的，这里需要用户提供
                connection.execute(insert_stu_detail,
                                   {'user_id': user_data.user_id, 'stu_num': ''})
            elif user_data.role == 'teacher':
                # 插入教师详细信息到teacher_detail表
                insert_teacher_detail = text(
                    """
                    INSERT INTO teacher_detail (uid, teacher_num) VALUES (:user_id, :teacher_num)
                    """
                )
                # 假设教师编号是必须的，这里需要用户提供
                connection.execute(insert_teacher_detail, {'user_id': user_data.user_id, 'teacher_num': ''})

        except SQLAlchemyError as e:
            # 如果发生错误，事务会自动回滚
            print(f"An error occurred: {e}")
            return False

    return True


